package com.basis.servlet;

import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URL;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.openbravo.base.secureApp.HttpSecureAppServlet;
import com.basis.utils.excel.ExcelHead;
import com.basis.utils.excel.ExcelWriteUtil;

public class DownImportTemplateServlet extends HttpSecureAppServlet {
	private static final long serialVersionUID = 7618658514199005769L;

	@Override
	public void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doPost(request, response);
	}

	@Override
	public void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		Connection conn = null;
		try {
			String code = request.getParameter("code");
			conn = this.getTransactionConnection();
			
			ExcelHead[][] heads = createTemplate(conn, code);
			ExcelWriteUtil util = new ExcelWriteUtil();
			util.createHead(heads);
			String savePath = request.getSession().getServletContext().getRealPath("/template");
			File file = new File(savePath);
			if (!file.exists() && !file.isDirectory()) {
				//创建目录
				file.mkdir();
			}
			savePath += "/" + code + ".xlsx";
			util.writeFile(savePath);
			
			downLoad(savePath, response, heads[0][0].getText() + ".xlsx", false);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
	            releaseRollbackConnection(conn);
			} catch (Exception e) {
			}
		}
	}
	
	private ExcelHead[][] createTemplate(Connection conn, String code) throws SQLException {
		String sql = "select c.name from Emes_Data_Import_Column c, Emes_Data_Import d " +
				"where c.emes_data_import_id = d.emes_data_import_id " +
				"and d.code = ? " +
				"order by c.seq";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, code);
		ResultSet rs = ps.executeQuery();
		String name = null;
		
		List<ExcelHead> list = new ArrayList<ExcelHead>();
		
		while (rs.next()) {
			name = rs.getString("name");
			list.add(new ExcelHead(name));
		}
		rs.close();
		ps.close();
		
		sql = "select d.name from Emes_Data_Import d where d.code = ?";
		ps = conn.prepareStatement(sql);
		ps.setString(1, code);
		rs = ps.executeQuery();
		while (rs.next()) {
			name = rs.getString("name");
		}
		rs.close();
		ps.close();
		
		ExcelHead [][]heads = new ExcelHead[2][list.size()];
		ExcelHead head = new ExcelHead(name);
		head.setColSpan(list.size());
		heads[0] = new ExcelHead[]{head};
		heads[1] = list.toArray(heads[1]);
		return heads;
	}
	
    /**
	 * 下载
	 * @param filePath文件路径
	 * @param isOnLine是否在线打开
	 */
	public void downLoad(String filePath, HttpServletResponse response, String fileName, boolean isOnLine) throws Exception {
		File f = new File(filePath);
		if (!f.exists()) {
			response.sendError(404, "File not found!");
			return;
		}
		BufferedInputStream br = null;
		OutputStream out = null;
		try {
			br = new BufferedInputStream(new FileInputStream(f));
			String name = "";
			if (fileName != null) {
				name = new String(fileName.getBytes("GBK"), "iso8859-1"); // 修改编码
			}
			byte[] buf = new byte[1024];
			int len = 0;
			response.reset(); // 非常重要
			if (isOnLine) { // 在线打开方式
				URL u = new URL("file:///" + filePath);
				response.setContentType(u.openConnection().getContentType());
				response.setHeader("Content-Disposition", "inline; filename=" + name);
				// 文件名应该编码成UTF-8
			} else { // 纯下载方式
				response.setContentType("application/x-msdownload");
				response.setHeader("Content-Disposition", "attachment; filename=" + name);
			}
			out = response.getOutputStream();
			while ((len = br.read(buf)) > 0) {
				out.write(buf, 0, len);
			}
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("下载文件失败!" + "--" + filePath);
		} finally {
			try {
				if (br != null) {
					br.close();
				}
			} catch (Exception ex) {
			}
			try {
				if (out != null) {
					out.close();
				}
			} catch (Exception ex) {
			}
		}
	}
}
